package fictional.DAL;


import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.TreeSet;
import java.util.SortedSet;
import java.util.Date;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;


import fictional.Common.ArticleItem;
import fictional.Common.CharacterItem;
import fictional.Common.FriendRequestItem;
import fictional.Common.FriendRequests;
import fictional.Common.GenderItem;
import fictional.Common.IDAL;
import fictional.Common.ImageItem;
import fictional.Common.MessageItem;
import fictional.Common.Messages;
import fictional.Common.OccupationItem;
import fictional.Common.OrganizationItem;
import fictional.Common.PlaceItem;
import fictional.Common.PokeItem;
import fictional.Common.Pokes;
import fictional.Common.PowerItem;
import fictional.Common.RankItem;
import fictional.Common.Response;
import fictional.Common.ShortCharacterItem;
import fictional.Common.SpeciesItem;
import fictional.Common.User;
import fictional.Common.Wall;
import fictional.Common.WallItem;


public class DAL implements IDAL {

	private static final String FREEBASE_RAW_PATH = "http://api.freebase.com/api/trans/raw";
	private static Connection con = null;
	
	// We need this to turn the Date item into a string format so we can insert it.
	private static DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	public DAL(String connectionString, String username, String password) 
		throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
			Class.forName("com.mysql.jdbc.Driver").newInstance ();
			con = DriverManager.getConnection(connectionString, username, password);	
	}
	
	@Override
	// Change the character's status message
	public void editStatus(String status, int charId, Response response) {
		try {
			execute("UpdateStatus", new Object[] { charId, status });
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		response.setIs_Success(true);
	}

	@Override
	// Get all information about the character
	public CharacterItem getCharacterById(int charId, Response response) {
		CharacterItem ci = null;
		try {
			ResultSet rs = getDataSet("GetCharacterDetails", new Object[] { charId } );
			// get the first result
			rs.next();
    		response.setIs_Success(true);
			ci = parseAllCharacterDetails(charId, response, rs);
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		if (ci == null) {
			response.setIs_Success(false);
		}
		return ci;
	}

	@Override
	// Get minimal information about the character
	public ShortCharacterItem getShortCharacterById(int charId, Response response) {
		ShortCharacterItem sci = null;
		try {
			ResultSet rs = getDataSet("GetCharacterDetails", new Object[] { charId } );
			// get the first result
			rs.next();
    		response.setIs_Success(true);
			sci = parseShortCharacterDetails(charId, response, rs);
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		if (sci == null) {
			response.setIs_Success(false);
		}
		return sci;
	}
	
	@Override
	// Returns all characters whose name contains name (for searching for people)
	public ArrayList<ShortCharacterItem> getShortCharacterByName(String name, Response response) {
        ArrayList<ShortCharacterItem> characterList = new ArrayList<ShortCharacterItem>();
		try {
			ResultSet characterDetailsResultSet = 
				getDataSet("GetCharacterDetailsByName", new Object[] { name } );

	        while (characterDetailsResultSet.next()) {
				ShortCharacterItem sci = null;
	            int charId = characterDetailsResultSet.getInt("character_id");
				sci = parseShortCharacterDetails(charId, response, characterDetailsResultSet);
	            characterList.add(sci);
				if (sci == null) {
					response.setIs_Success(false);
				}
	        }
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return characterList;
	}

	
	// Creates a database entry for a new character just with the name
	public int insertCharacter(int userId, String charName, Response response)
	{
		try {
			ResultSet rs = getDataSet("InsertCharacterBasic",
					new Object[] { userId, charName } );
			
			if (rs.next()) {
				int id = rs.getInt("id");
				response.setIs_Success(true);
				return id; 
			}
		} catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return OPERATION_FAILED;
	}


	@Override
	// Gets the users default character (for logging in)
	public CharacterItem getDefaultCharacter(int userId, Response response) {
		
		try {
	        ResultSet rs = getDataSet("GetDefaultCharacter", new Object[] { userId } );

	        rs.next();
			response.setIs_Success(true);
            int characterId = rs.getInt("character_id");
            return getCharacterById(characterId, response);
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return null;
	}

	@Override
	public boolean isAllowedEditingCharacter(int charId, int userId, Response response) {
		try {
	        ResultSet rs = getDataSet("GetDefaultCharacter", new Object[] { userId } );

	        rs.next();
			response.setIs_Success(true);
            int userCharacterId = rs.getInt("character_id");
            
            return charId == userCharacterId;
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return false;
	}

	@Override
	// Returns the user id or -1 if the login doesn't exist
	public int login(String username, String password, Response response) {
		try {
	        ResultSet rs = getDataSet("LoginExists", new Object[] { username, password } );

	        while (rs.next()) {
				response.setIs_Success(true);
	        	return rs.getInt("user_id");
	        }
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return OPERATION_FAILED;
	}

	@Override
	// Removes friendships between charId and friendId
	// Return unsuccessfully if there's no friendship between those characters
	public void deleteFriend(int charId, int friendId, Response response) {
		try {
			response.setIs_Success(true);
			ResultSet rs = getDataSet("DeleteFriend", new Object[] { charId, friendId });
	        while (rs.next()) {
	        	if (rs.getInt("rowcount") < 1) {
					response.setIs_Success(false);
		        }
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
	}
	
	@Override
	// Delete a specific wall post
	// Returns unsuccessfully if the post doesn't exist in the database
	public void deleteWallPost(WallItem wallPost, Response response) {
		response.setIs_Success(true);
		deleteSocialItem("DeleteWallPost", new Object[] { wallPost.getId() }, response);
	}
	
	
	@Override
	// Returns the wall posts which character has received
	public Wall getWall(CharacterItem character, Response response) {
        Wall wall = new Wall(character);
        SortedSet<WallItem> messageCollection = new TreeSet<WallItem>();
        try {
            response.setIs_Success(true);
            ResultSet rs = getDataSet("GetReceivedWallMessages", new Object[] { character.getId() });
            Hashtable<Integer, ShortCharacterItem> sentCharacters = new Hashtable<Integer, ShortCharacterItem>();
            
            while (rs.next()) {
                int id = rs.getInt("character_wall_id");
                int senderId = rs.getInt("sender_id");
                // Parse the date result into an actual Date
                Date posted = df.parse(rs.getString("posted"));
                String body = rs.getString("body");
                
                // save ShortCharacterItems so we don't have to get them multiple times 
                // (if multiple messages were received from this character)
                ShortCharacterItem tempCharacter;
                if (null == (tempCharacter = (ShortCharacterItem) sentCharacters.get(senderId))) {
                    tempCharacter = getShortCharacterById(senderId, response); 
                    sentCharacters.put(senderId, tempCharacter);
                }
                WallItem wi = new WallItem(id, posted,
                        tempCharacter,
                        character,
                        body);
                if (!response.isIs_Success()) {
                    break;
                }
                messageCollection.add(wi);
            }
        }
        catch (SQLException ex) {
            response.setIs_Success(false);
            response.setException(ex);
        } catch (ParseException ex) {
            response.setIs_Success(false);
            response.setException(ex);
        }
        
        wall.setWallPosts(messageCollection);
        return wall;

	}
		
	@Override
	// Creates a wall post
	// Return value is the newly inserted row id 
	public int postToWall(WallItem wallItem, Response response) {
    	response.setIs_Success(true);
		int res =  insertSocialItem("InsertToWall",
			new Object[] {  wallItem.getSender().getId(), wallItem.getReceiver().getId(),
							wallItem.getText(), wallItem.getDate() },
			response);
		wallItem.setId(res);
		return res;
	}

	@Override
	// Returns the messages which character has received
	// if getReceivedincoming is true - return messages where character is the receiver
	// otherwise message where character is the sender
	public Messages getMessages(CharacterItem character, boolean getReceived, Response response) {
		Messages messages = new Messages(character);
		SortedSet<MessageItem> messageCollection = new TreeSet<MessageItem>();
		try {
	    	response.setIs_Success(true);
	    	ResultSet rs;
	    	if (getReceived) {
		        rs = getDataSet("GetReceivedPrivateMessages", 
		        		new Object[] { character.getId() });
	    	}
	    	else {
		        rs = getDataSet("GetSentPrivateMessages", 
		        		new Object[] { character.getId() });
	    	}
	    		
	        Hashtable<Integer, ShortCharacterItem> characters = new Hashtable<Integer, ShortCharacterItem>();
	
	        while (rs.next()) {
	        	
	            int id = rs.getInt("private_message_id");
	            int senderId = rs.getInt("sender_id");
	            int receiverId = rs.getInt("receiver_id");
	            //sanity check
	            if (character.getId() != (getReceived ? receiverId : senderId)) {
	            	continue;
	            }
	            // Parse the date result into an actual Date
	            Date posted = df.parse(rs.getString("posted"));
	            String body = rs.getString("body");
	            Boolean read = rs.getInt("read") == 1;
	            
	            // save ShortCharacterItems so we don't have to get them multiple times 
	            // (if multiple messages were received from this character)
	            ShortCharacterItem tempCharacter;
	            if (null == (tempCharacter = (ShortCharacterItem) characters.get(senderId))) {
	    			tempCharacter = getShortCharacterById(senderId, response); 
	    			characters.put(senderId, tempCharacter);
	            }
	        	MessageItem mi = new MessageItem(id,
	        			tempCharacter,
	        			character,
	        			posted, body, read);
	        	if (!response.isIs_Success()) {
	        		break;
	        	}
	        	messageCollection.add(mi);
	        }
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		} catch (ParseException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		
		messages.setMessages(messageCollection);
		return messages;
	}

	@Override
	// Sends a message
	public int sendMessage(MessageItem message, Response response) {
		response.setIs_Success(true);
		int res =  insertSocialItem("InsertToPrivateMessage",
			new Object[] {  message.getSender().getId(), message.getReceiver().getId(),
							message.getMessage(), message.getDate() },
			response);
		message.setId(res);
		return res;
	}

	@Override
	// Deletes a message
	// Returns unsuccessfully if the message doesn't exist in the database
	public void deleteMessage(int messageId, Response response) {
		deleteSocialItem("DeletePrivateMessage", new Object[] { messageId }, response);
	}

	@Override
	// Gets all the characters friend requests
	public FriendRequests getFriendRequests(CharacterItem character, boolean getReceived, Response response) {
		SortedSet<FriendRequestItem> requests = null;
		try {
			response.setIs_Success(true);
			
	    	response.setIs_Success(true);
	    	ResultSet rs;
	    	if (getReceived) {
		        rs = getDataSet("GetReceivedFriendRequests", 
		        		new Object[] { character.getId() });
	    	}
	    	else {
		        rs = getDataSet("GetSentFriendRequests", 
		        		new Object[] { character.getId() });
	    	}
			requests = new TreeSet<FriendRequestItem>();
	
	        while (rs.next()) {
	            int id = rs.getInt("friend_request_id");
	            int requesterId = rs.getInt("requester_id");
	            int confirmerId = rs.getInt("confirmer_id");
	            ShortCharacterItem requester = getReceived ? getShortCharacterById(requesterId, response) : character;
	            ShortCharacterItem confirmer = getReceived ? character : getShortCharacterById(confirmerId, response);
	            Date date = df.parse(rs.getString("request_date"));
	            
	            requests.add(new FriendRequestItem(id, requester, confirmer, date));
	        }
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		} catch (ParseException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		
		FriendRequests res = new FriendRequests(character);
		res.setRequests(requests);
		return res;
	}

	@Override
	// Sends a friend request
	public int sendFriendRequest(FriendRequestItem request, Response response) {
		try {
			response.setIs_Success(true);
			ResultSet rs = getDataSet("AddFriendRequest", new Object[] { request.getSender().getId(), 
					request.getReceiver().getId(), request.getDate() });
			
			rs.next();
			int newId = rs.getInt("id");
			response.setIs_Success(true);
			
			request.setId(newId);
			return newId;
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return -1;
	}

	@Override
	// Deletes the friend request described in request
	public void deleteFriendRequest(FriendRequestItem request, Response response) {
		try {
			response.setIs_Success(true);
			execute("DeleteFriendRequest", new Object[] { request.getId() });
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		
	}

	@Override
	// Adds a friendship between character with firstId and character with secondId
	public void addFriendship(int firstId, int secondId, Response response) {
		try {
			response.setIs_Success(true);
			execute("AddFriend", new Object[] { firstId, secondId });
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
	}
	
	@Override
	// Find similar characters two suggest as friends
	public ArrayList<ShortCharacterItem> getSimilarCharacters(int charId,Response response) {
		ArrayList<ShortCharacterItem> suggested = new ArrayList<ShortCharacterItem>();
		try {	        
			ResultSet rs = 
	        	getDataSet("GetSuggestedCharacters", new String[] { Integer.toString(charId) } );

	        while (rs.next()) {
	        	int characterId = rs.getInt("character_id");
	        	suggested.add(getShortCharacterById(characterId, response));
	        }                        
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return suggested;
	}

	@Override
	public ArrayList<ShortCharacterItem> getUnfriendedRelatives(int charId,Response response) {
		ArrayList<ShortCharacterItem> suggested = new ArrayList<ShortCharacterItem>();
		try {	        
			ResultSet rs = 
	        	getDataSet("GetUnfriendedRelatives", new String[] { Integer.toString(charId) } );

	        while (rs.next()) {
	        	int characterId = rs.getInt("character1_id");
	        	suggested.add(getShortCharacterById(characterId, response));
	        }                        
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return suggested;
	}

	@Override
	public List<GenderItem> getAllGenders(Response response) {
		List<GenderItem> genders = new ArrayList<GenderItem>();
		try {
			ResultSet rs = getDataSet("GetAllGenders", null);
			
	        while (rs.next()) {
	            int id = rs.getInt("gender_id");
	            String mid = rs.getString("mid");
	            String name = rs.getString("gender");
	            GenderItem gi = new GenderItem(id, mid);
	            gi.setGender(name);
	            genders.add(gi);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return genders;
	}

	@Override
	public List<OrganizationItem> getAllOrganizations(Response response) {
		List<OrganizationItem> organizations = new ArrayList<OrganizationItem>();
		try {
			ResultSet rs = getDataSet("GetAllOrganizations", null);
			
	        while (rs.next()) {
	            int id = rs.getInt("organization_id");
	            String mid = rs.getString("mid");
	            String name = rs.getString("name");
	            OrganizationItem oi = new OrganizationItem(id, mid);
	            oi.setName(name);
	            organizations.add(oi);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return organizations;
	}

	@Override
	public List<PlaceItem> getAllPlaces(Response response) {
		List<PlaceItem> places = new ArrayList<PlaceItem>();
		try {
			ResultSet rs = getDataSet("GetAllPlaces", null);
			
	        while (rs.next()) {
	            int id = rs.getInt("place_id");
	            String mid = rs.getString("mid");
	            String name = rs.getString("place");
	            PlaceItem pi = new PlaceItem(id, mid);
	            pi.setPlace(name);
	            places.add(pi);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return places;
	}

	@Override
	public List<PowerItem> getAllPowers(Response response) {
		List<PowerItem> powers = new ArrayList<PowerItem>();
		try {
			ResultSet rs = getDataSet("GetAllPowers", null);
			
	        while (rs.next()) {
	            int id = rs.getInt("power_id");
	            String mid = rs.getString("mid");
	            String name = rs.getString("power");
	            PowerItem pi = new PowerItem(id, mid);
	            pi.setPower(name);
	            powers.add(pi);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return powers;
	}

	@Override
	public List<SpeciesItem> getAllSpecies(Response response) {
		List<SpeciesItem> species = new ArrayList<SpeciesItem>();
		try {
			ResultSet rs = getDataSet("GetAllSpecies", null);
			
	        while (rs.next()) {
	            int id = rs.getInt("species_id");
	            String mid = rs.getString("mid");
	            String name = rs.getString("species");
	            SpeciesItem si = new SpeciesItem(id, mid);
	            si.setSpecies(name);
	            species.add(si);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return species;
	}

	@Override
	// Sets a specific message as read
	public void readMessage(int messageId, Response response) {
		try {
		execute("SetMessageRead", new Object [] { messageId } );
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
	}
	
	@Override
	// Inserts a new user to the database
	public int insertUser(User user, Response response) {
		try {
			ResultSet rs = getDataSet("InsertUser",
					new Object[] {  user.getUsername(), user.getPassword()}); 
			if (rs.next()) {
				int new_id = rs.getInt(1);
				response.setIs_Success(true);
				return new_id;
			}
			else
				response.setIs_Success(false);
		}
		catch (MySQLIntegrityConstraintViolationException ex) {
			response.setIs_Success(false);
			response.setException(ex);
			response.setMessage("Desired username " + user.getUsername() + " already exists");
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return OPERATION_FAILED;
	}

	@Override
	// Updates the characters details according to the user input
	public void updateCharacterDetails(CharacterItem character, Response response) {
		try {
			execute("UpdateCharacter", new Object[] { character.getId(), character.getPlace_of_birth().getId(),
													  character.getGender().getId(),character.getSpecies().getId()});
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
		response.setIs_Success(true);
	}

	@Override
	public int insertCharacterImage(CharacterItem character, String imageLink,
			Response response) {
		try {
			ResultSet rs = getDataSet("InsertImage", new Object[] { character.getId(), imageLink});
			if (rs.next()) {				
				return rs.getInt(1);
			}
			else
				response.setIs_Success(false);
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return OPERATION_FAILED;
	}

	@Override
	public void insertPoke(PokeItem poke,Response response) {
		try {
			execute("InsertPoke", new Object[] { poke.getSender().getId() , poke.getReceiver().getId(), poke.getDate() });
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
	}
	
	@Override
	public void deleteCharacterImage(ImageItem img, Response response) {
		try {
			execute("DeleteCharacterImage", new Object[] { img.getId() });
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
	}	
	
	@Override
	public void setProfileImage(ImageItem img, CharacterItem character,
			Response response) {
		try {
			execute("SetProfileImage", new Object[] { img.getId(), character.getId() });
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
	}

	@Override
	public Pokes getPokes(CharacterItem character,
			Response response) {
		Pokes pokes = new Pokes(character);		
		try {
			response.setIs_Success(true);
	        ResultSet rs = getDataSet("GetPokes", new Object[] { character.getId() });	        
	        
	        while (rs.next()) {
	            int id = rs.getInt("poke_id");
	            int senderId = rs.getInt("sender_id");
	            Date posted = df.parse(rs.getString("posted"));

	        	PokeItem pi = new PokeItem(id, getShortCharacterById(senderId, response), 
	        			character,
	        			posted);
	        	pokes.addPoke(pi);
	        }
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		} catch (ParseException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}

    	return pokes;
	}		
	
	
	@Override
	public OccupationItem insertOccupation(String occupation,Response response) {
		response.setIs_Success(true);
		try {
			ResultSet rs = getDataSet("InsertOccupation", new Object[] { occupation });
			if (rs.next()) {
				int id = rs.getInt("occupation_id");
				String mid = rs.getString("mid");
				String name = rs.getString("occupation");
				OccupationItem oi = new OccupationItem(id,mid,name);
				return oi;
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
		return null;
	}
	

	@Override
	public ArrayList<OccupationItem> getAllOccupations(Response response) {
		ArrayList<OccupationItem> list = new ArrayList<OccupationItem>();
		try {
			ResultSet rs = getDataSet("GetAllOccupations", new Object[] {});
			while (rs.next()) {
				String name = rs.getString("occupation");
				String mid = rs.getString("mid");
				int id = rs.getInt("occupation_id");
				list.add(new OccupationItem(id,mid,name));
			}
			return list;			
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return null;
	}
		

	@Override
	public OrganizationItem insertOrganization(String organization,Response response) {
		response.setIs_Success(true);
		try {
			ResultSet rs = getDataSet("InsertOrganization", new Object[] { organization });
			if (rs.next()) {
				int id = rs.getInt("organization_id");
				String mid = rs.getString("mid");
				String name = rs.getString("name");
				OrganizationItem oi = new OrganizationItem(id,mid,name);
				return oi;
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
		return null;
	}

	@Override
	public PowerItem insertPower(String power,Response response) {
		response.setIs_Success(true);
		try {
			ResultSet rs = getDataSet("InsertPower", new Object[] { power });
			if (rs.next()) {
				int id = rs.getInt("power_id");
				String mid = rs.getString("mid");
				String name = rs.getString("power");
				PowerItem pi = new PowerItem(id,mid,name);
				return pi;
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
		return null;
	}

	// Updates a characters occupations 
	public void updateOccupations(CharacterItem ci, List<OccupationItem> occupations, Response response)
	{
		try {
			List<OccupationItem> existingOccupations = getOccupations(ci.getId(), response);
			List<Integer> existingIds = new ArrayList<Integer>();
			for ( OccupationItem occupation : existingOccupations ) {
				existingIds.add(occupation.getId());
			}
			for ( OccupationItem occupation : occupations ) {
				int currentId = occupation.getId();
				if (!existingIds.contains(currentId)) {
					execute("InsertCharacterOccupation", new Object[] { ci.getId(), currentId });
				} else {
					existingIds.remove((Integer)currentId);
				}
			}
			for ( int id : existingIds ) {
				execute("DeleteCharacterOccupation", new Object[] { ci.getId(), id });
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
	}
	
	// Updates a characters organizations 
	public void updateOrganizations(CharacterItem ci, List<OrganizationItem> organizations, Response response)
	{
		try {
			List<OrganizationItem> existingOrganizations = getOrganizations(ci.getId(), response);
			List<Integer> existingIds = new ArrayList<Integer>();
			for ( OrganizationItem organization : existingOrganizations ) {
				existingIds.add(organization.getId());
			}
			for ( OrganizationItem organization : organizations ) {
				int currentId = organization.getId();
				if (!existingIds.contains(currentId)) {
					execute("InsertCharacterOrganization", new Object[] { ci.getId(), currentId });
				} else {
					existingIds.remove((Integer)currentId);
				}
			}
			for ( int id : existingIds ) {
				execute("DeleteCharacterOrganization", new Object[] { ci.getId(), id });
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
	}
	
	// Updates a characters powers 
	public void updatePowers(CharacterItem ci, List<PowerItem> powers, Response response)
	{
		try {
			List<PowerItem> existingPowers = getPowers(ci.getId(), response);
			List<Integer> existingIds = new ArrayList<Integer>();
			for ( PowerItem power : existingPowers ) {
				existingIds.add(power.getId());
			}
			for ( PowerItem power : powers ) {
				int currentId = power.getId();
				if (!existingIds.contains(currentId)) {
					execute("InsertCharacterPower", new Object[] { ci.getId(), currentId });
				} else {
					existingIds.remove((Integer)currentId);
				}
			}
			for ( int id : existingIds ) {
				execute("DeleteCharacterPower", new Object[] { ci.getId(), id });
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}	
	}
	
	// parses the first row of the characterDetailsResultSet
	private ShortCharacterItem parseShortCharacterDetails(int charId, Response response, ResultSet rs) {
		ShortCharacterItem sci = null;
		try {
				
	        String name = rs.getString("name");
	        String mid = rs.getString("mid");
	        sci = new ShortCharacterItem(charId, mid);
	        
			sci.setName(name);    		
	        String imageMid = rs.getString("image_mid");
	        if (imageMid != null) {
	        	int imageId = rs.getInt("image_id");
		        Boolean imageUserCreated = rs.getInt("image_user_created") == 1;
		        ImageItem ii = new ImageItem(imageId,imageMid);
		        ii.setIs_profile(true);
		        ii.setUserCreated(imageUserCreated);
		        if (imageUserCreated)
		        	ii.setLink(imageMid);	            		            
		        else
		        	ii.setLink(FREEBASE_RAW_PATH + imageMid);	            	            
		        sci.setProfileImage(ii);
	        }	        
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return sci;
	}

	private CharacterItem parseAllCharacterDetails(int charId, Response response, ResultSet rs) {
		
		CharacterItem ci = null;
		ShortCharacterItem sci;
		try {
			sci = parseShortCharacterDetails(charId, response, rs);
	        String name = rs.getString("name");
	        String mid = rs.getString("mid");
	        float height = rs.getFloat("height");
	        float weight = rs.getFloat("weight");
	        String quotations = rs.getString("quotations");
	        String status = rs.getString("status");
	        int gender = rs.getInt("gender_id");
	        String genderMid = rs.getString("gender_mid");
	        String genderName = rs.getString("gender_name");
	        int species = rs.getInt("species_id");
	        String speciesMid = rs.getString("species_mid");
	        String speciesName = rs.getString("species_name");
	        int place = rs.getInt("place_id");
	        String placeMid = rs.getString("place_mid");
	        String placeName = rs.getString("place_name");
			PlaceItem pi = new PlaceItem(place, placeMid);
			pi.setPlace(placeName);
	        
			ci = new CharacterItem(charId, mid);
			ci.setProfileImage(sci.getProfileImage());
			ci.setName(name);    		
			ci.setHeight(height);
			ci.setWeight(weight);
			ci.setStatus(status);
			ci.setQuotations(quotations);
			ci.setSpecies(new SpeciesItem(species, speciesMid,speciesName));
			ci.setGender(new GenderItem(gender, genderMid,genderName));
			ci.setPlace_of_birth(pi);
			
			ci.setChildren(getChildren(charId, response));
			ci.setMarriages(getSpouses(charId, response));
			ci.setFriends(getFriends(charId, response));
			ci.setRomantics(getRomantics(charId, response));
			ci.setSiblings(getSiblings(charId, response));
			ci.setArticle(getArticle(charId, response));
			ci.setImages(getImages(charId, response));
			ci.setOccupations(getOccupations(charId, response));
			ci.setOrganizations(getOrganizations(charId, response));
			ci.setPowers(getPowers(charId, response));
			ci.setRank(getRank(charId, response));
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return ci;
	}

	// Use this to simplify getting result sets. Just use the procedure name, and the parameter in an array.
	private ResultSet getDataSet(String procedureName, Object[] params) throws SQLException {
        CallableStatement cs = createCommand(procedureName, params);
        return cs.executeQuery();
	}
	
	// Use this to simplify executing commands. Just use the procedure name, and the parameter in an array.
	private void execute(String procedureName, Object[] params) throws SQLException {
        CallableStatement cs = createCommand(procedureName, params);
        
        cs.execute();
	}
	
	private CallableStatement createCommand(String commandName, Object[] params) throws SQLException {
		String command = "{call " + commandName + "(";
		
		if ((params != null) && (params.length > 0))  {
			for (int i = 0; i < params.length; i++) {
				command += ((i > 0) ? "," : "") + "?";
			}
		}
		command += ")}";
		
		CallableStatement cs = con.prepareCall(command);
		
		if ((params != null) && (params.length > 0))  {
			for (int i = 0; i < params.length; i++) {
				if (params[i].getClass().equals(String.class)) {
					cs.setString(i + 1, params[i].toString());
				}
				else if (params[i].getClass().equals(Date.class)) {
					java.util.Date orig = (java.util.Date) params[i];
					String date = df.format(orig);
					cs.setString(i + 1, date);
				}
				else {
					cs.setString(i + 1, params[i].toString());
				}
			}
		}
		return cs;
	}
	
	// Used for sendMessage and postToWall since they have a similar command format
	private int insertSocialItem(String command, Object[] params, Response response)
	{
		try {
			ResultSet rs = getDataSet(command, params); 
			while (rs.next()) {
				int new_id = rs.getInt(1);
				response.setIs_Success(true);
				return new_id;
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return OPERATION_FAILED;
	}
	
	// Used for deleteMessage and deleteWallPost since they have a similar command format
	private void deleteSocialItem(String command, Object[] params, Response response) {
		try {
			ResultSet rs = getDataSet(command, params); 
			while (rs.next()) {
	        	if (rs.getInt("rowcount") < 1)
					response.setIs_Success(false);
			}
			execute(command, params);
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
	}
	
	private ArrayList<ShortCharacterItem> getSiblings(int charId, Response response) {
		return getRelations("GetCharacterSiblings", charId, response);
	}
	
	
	private ArrayList<ShortCharacterItem> getFriends(int charId, Response response) {
		return getRelations("GetCharacterFriends", charId, response);
	}
	
	private ArrayList<ShortCharacterItem> getRomantics(int charId, Response response) {
		return getRelations("GetCharacterRomantics", charId, response);
	}
	
	private ArrayList<ShortCharacterItem> getSpouses(int charId, Response response) {
		return getRelations("GetCharacterSpouses", charId, response);
	}
	
	
	private ArrayList<ShortCharacterItem> getChildren(int charId, Response response) {
		return getRelations("GetCharacterChildren", charId, response);
	}
	
	// This is used to get groups of relations for a specific character
	private ArrayList<ShortCharacterItem> getRelations(String command, int charId, Response response) {
		ArrayList<ShortCharacterItem> relations = new ArrayList<ShortCharacterItem>();
		try {
			ResultSet rs = getDataSet(command, new Object[] { charId } );
			
			while (rs.next()) {
	            int id = rs.getInt("character_id");
				relations.add(parseShortCharacterDetails(id, response, rs));
				if (!response.isIs_Success())
					break;
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return relations;
	}
	
	private ArticleItem getArticle(int charId, Response response) {
		try {
			ResultSet rs = getDataSet("GetOneArticle", new Object[] { charId } );
			while (rs.next()) {
	            int id = rs.getInt("character_article_id");
	            String mid = rs.getString("mid");
				return new ArticleItem(id, mid);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return null;
	}
	
	private ArrayList<ImageItem> getImages(int charId, Response response) {
		ArrayList<ImageItem> images = new ArrayList<ImageItem>();
		try {
			ResultSet rs = getDataSet("GetImages", new Object[] { charId } );
			while (rs.next()) {
	            int id = rs.getInt("image_id");
	            Boolean isProfile = rs.getInt("profile") == 1;
	            Boolean userCreated = rs.getInt("user_created") == 1;
	            String mid = rs.getString("mid");
	            if (mid != null) {
		            ImageItem ii = new ImageItem(id,mid);	
		            ii.setUserCreated(userCreated);
		            ii.setIs_profile(isProfile);
		            if (userCreated)
		            	ii.setLink(mid);
		            else
		            	ii.setLink(FREEBASE_RAW_PATH + mid);
		            images.add(ii);
	            }
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return images;
	}
	
	private ArrayList<OccupationItem> getOccupations(int charId, Response response)
	{
		ArrayList<OccupationItem> occupations = new ArrayList<OccupationItem>();
		try {
			ResultSet rs = getDataSet("GetOccupations", new Object[] { charId } );
			while (rs.next()) {
	            int id = rs.getInt("occupation_id");
	            String name = rs.getString("occupation");
	            String mid = rs.getString("mid");
	            OccupationItem oi = new OccupationItem(id, mid);
	            oi.setOccupation(name);
	            occupations.add(oi);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return occupations;
	}
	
	
	private ArrayList<OrganizationItem> getOrganizations(int charId, Response response)
	{
		ArrayList<OrganizationItem> organizations = new ArrayList<OrganizationItem>();
		try {
			ResultSet rs = getDataSet("GetOrganizations", new Object[] { charId } );
			while (rs.next()) {
	            int id = rs.getInt("organization_id");
	            String name = rs.getString("name");
	            String mid = rs.getString("mid");
	            OrganizationItem oi = new OrganizationItem(id, mid);
	            oi.setName(name);
	            organizations.add(oi);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return organizations;
	}
	
	private ArrayList<PowerItem> getPowers(int charId, Response response)
	{
		ArrayList<PowerItem> powers = new ArrayList<PowerItem>();
		try {
			ResultSet rs = getDataSet("GetPowers", new Object[] { charId } );
			while (rs.next()) {
	            int id = rs.getInt("power_id");
	            String name = rs.getString("power");
	            String mid = rs.getString("mid");
	            PowerItem pi = new PowerItem(id, mid);
	            pi.setPower(name);
	            powers.add(pi);
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return powers;
	}
	
	private RankItem getRank(int charId, Response response)
	{
		try {
			ResultSet rs = getDataSet("GetOneRank", new Object[] { charId } );
			while (rs.next()) {
	            int id = rs.getInt("rank_id");
	            String name = rs.getString("rank");
	            String mid = rs.getString("mid");
	            RankItem ri = new RankItem(id, mid);
	            ri.setRank(name);
	            return ri;
			}
		}
		catch (SQLException ex) {
			response.setIs_Success(false);
			response.setException(ex);
		}
		return new RankItem(-1, "", "");
	}

	


	
}
